-- 创建 pgvector 扩展
CREATE EXTENSION IF NOT EXISTS vector;

-- 创建 knowledge_base 表
CREATE TABLE public.knowledge_base (
	id serial4 NOT NULL,
	name varchar(100) NOT NULL,
	description text NULL,
	embedding_model varchar(50) NOT NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT knowledge_base_pkey PRIMARY KEY (id)
);

-- 创建 document 表
CREATE TABLE public.document (
	id serial4 NOT NULL,
	knowledge_base_id int4 NOT NULL,
	title varchar(200) NOT NULL,
	content text NOT NULL,
	embedding public.vector(1024) NULL,
	doc_metadata json NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT document_pkey PRIMARY KEY (id),
	CONSTRAINT document_knowledge_base_id_fkey FOREIGN KEY (knowledge_base_id) REFERENCES knowledge_base(id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX idx_document_embedding ON public.document USING ivfflat (embedding vector_cosine_ops) WITH (lists='100');
CREATE INDEX idx_document_knowledge_base_id ON public.document USING btree (knowledge_base_id);

-- 创建更新时间触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
	NEW.updated_at = CURRENT_TIMESTAMP;
	RETURN NEW;
END;
$$ language 'plpgsql';

-- 为表添加触发器
CREATE TRIGGER update_knowledge_base_updated_at
	BEFORE UPDATE ON knowledge_base
	FOR EACH ROW
	EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_document_updated_at
	BEFORE UPDATE ON document
	FOR EACH ROW
	EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE public.system_prompt (
	id serial4 NOT NULL,
	"name" varchar(100) NOT NULL,
	"content" text NOT NULL,
	description text NULL,
	is_default bool DEFAULT false NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL
);